﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using BusinessLogic;
using System.Data.SqlClient;
using System.Web.Security;

namespace DataAccessLogic
{
    public static class PhoneDataAccess 
    {
        #region Methods
        private static Phone BindRowToPhone(DataRow row)
        {
            Phone p = new Phone();
            p.ID = DataAccess.CheckForGuidColumn(row, "ID");
            p.Number = DataAccess.CheckForStringColumn(row, "NUMBER");
            p.Extension = DataAccess.CheckForStringColumn(row, "EXTENSION");
            p.IsActive = DataAccess.CheckForBooleanColumn(row, "ISACTIVE");
            p.IsPrimary = DataAccess.CheckForBooleanColumn(row, "ISPRIMARY");

            PhoneType pt = new PhoneType();
            pt.ID = DataAccess.CheckForGuidColumn(row, "PHONETYPECODEID");
            pt.Description = DataAccess.CheckForStringColumn(row, "PHONETYPE");
            p.PhoneType = pt;

            return p;
        }

        public static Guid CreateForConversion(Phone phone, Guid constituentID)
        {
            return Create(phone, constituentID, true);
        }

        public static Guid Create(Phone phone, Guid constituentID)
        {
            return Create(phone, constituentID, false);
        }

        public static Guid Create(Phone phone, Guid constituentID, bool isConversion)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid();

            try
            {
                providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            }
            catch
            {
                providerUserKey = new Guid("B6E747BD-F3D5-482A-9785-9FEF877D5B6C");
            }

            if(isConversion)
            {
                providerUserKey = new Guid("c9014ff9-ee89-4192-aa6b-b0c2130557ba");
            }

            string outputParameterName = "@ID";

            if((phone != null) && (constituentID != Guid.Empty))
            {
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.Direction = ParameterDirection.Output;
                p.DbType = DbType.Guid;
                
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(p);
                parms.Add(new SqlParameter("@EXTENSION", phone.Extension));
                parms.Add(new SqlParameter("@PHONETYPECODEID", phone.PhoneType.ID));
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
                parms.Add(new SqlParameter("@NUMBER", phone.Number));
                parms.Add(new SqlParameter("@ISPRIMARY", phone.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));
                
                object obj = DataAccess.Create(StoredProcedures.USP_PHONE_CREATE, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        public static void Delete(Guid id)
        {
            if(id != Guid.Empty)
            {
                DataAccess.Delete(StoredProcedures.USP_PHONE_DELETE, id);
            }
        }
        
        public static void Delete(Phone phone)
        {
            if(phone != null)
            {
                Delete(phone.ID);
            }
        }

        public static void DeleteByConstituentID(Guid constituentID)
        {
            if(constituentID != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DataAccess.Delete(StoredProcedures.USP_PHONE_DELETEBYCONSTITUENTID, parms);
            }
        }

        private static List<Phone> Get(string storedProcedure, Guid constituentID)
        {
            List<Phone> phones = null;

            if(constituentID != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DataTable dt = DataAccess.Get(storedProcedure, parms);

                if((dt != null) && (dt.Rows.Count > 0))
                {
                    phones = new List<Phone>();

                    foreach (DataRow row in dt.Rows)
                    {
                        phones.Add(BindRowToPhone(row));
                    }
                }
            }

            return phones;
        }

        public static List<Phone> GetActiveByConstituentID(Guid constituentID)
        {
            return Get(StoredProcedures.USP_PHONE_GETACTIVEBYCONSTITUENTID, constituentID);           
        }

        public static List<Phone> GetAllByConstituentID(Guid constituentID)
        {
            return Get(StoredProcedures.USP_PHONE_GETALLBYCONSTITUENTID, constituentID);
        }

        public static Phone GetByID(Guid id)
        {
            Phone p = null;

            if(id != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", id));

                DataTable dt = DataAccess.Get(StoredProcedures.USP_PHONE_GETBYPHONEID, parms);

                if((dt != null) && (0 < dt.Rows.Count))
                {
                    p = BindRowToPhone(dt.Rows[0]);
                }
            }

            return p;
        }

        public static List<Phone> GetPrimaryByConstituentID(Guid constituentID)
        {
            return Get(StoredProcedures.USP_PHONE_GETPRIMARYBYCONSTITUENTID, constituentID);            
        }

        public static void Update(Phone phone, Guid constituentID)
        {
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());

            if ((phone != null) &&(constituentID != Guid.Empty))
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", phone.ID));
                parms.Add(new SqlParameter("@EXTENSION", phone.Extension));
                parms.Add(new SqlParameter("@PHONETYPECODEID", phone.PhoneType.ID));
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
                parms.Add(new SqlParameter("@NUMBER", phone.Number));
                parms.Add(new SqlParameter("@ISPRIMARY", phone.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                DataAccess.Update(StoredProcedures.USP_PHONE_UPDATE, parms);
            }
        }       
        #endregion
    }
}